#include "sqliteoperate.h"
#include <QDateTime>
QString SqliteOperate::generateRandomConnectionName()
{
    const QString chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
    const int length = 10;

    QString connectionName;

    // 生成随机字符串
    qsrand(QDateTime::currentMSecsSinceEpoch());
    for (int i = 0; i < length; ++i)
    {
        int index = qrand() % chars.length();
        connectionName.append(chars.at(index));
    }

    return connectionName;
}
SqliteOperate::SqliteOperate()
{
    open_database();
//    select_all_data_txt();
    qDebug() << "Sqlite open!!";
}

SqliteOperate::~SqliteOperate()
{
    qDebug() << "close_database()";
    close_database();
}
void SqliteOperate::open_database()
{
    QString connectionName = generateRandomConnectionName();
    qDebug()<<"connectionName="<<connectionName;
    database = QSqlDatabase::addDatabase("QSQLITE",connectionName);
    database.setDatabaseName("subway_qd.sqlite3");
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
}

void SqliteOperate::close_database()
{
    database.close();
    while(database.transaction()){
        qDebug() << "while(database.transaction())";
    }
    QString name = database.connectionName();//获得默认连接名。
    database= QSqlDatabase();
//    qDebug()<<"name"<<name;
    QSqlDatabase::removeDatabase(name);//删除默认连接。
    qDebug()<<"close_database() over!";

}

QList<QString> SqliteOperate::sqlite_select_cmd(QString select_sql)
{
    QList<QString> list;
    QSqlQuery sql_query(database); // 使用全局数据库连接对象

    sql_query.prepare(select_sql);
    if (!sql_query.exec())
    {
        qDebug() << "SqliteOperate::sqlite_select_cmd ERROR";
        return list;
    }
   // sql_query.exec(select_sql);
    QSqlRecord record = sql_query.record();
    while (sql_query.next())
    {
        for (int i = 0; i < record.count(); ++i)
        {
            list.append(sql_query.value(i).toString());
        }
    }

    return list;
}


int SqliteOperate::sqlite_commit_cmd(QString commit_sql){

    QSqlQuery sql_query(database);
    sql_query.prepare(commit_sql);
    if(!sql_query.exec())
    {
        return -1;
    }
    else
    {
//        select_all_data_txt();
        return  0;
    }
    return 0;
}


void SqliteOperate::file_insert(QString filename,QString data) {
    QFile file(filename);
    if (!file.open(QIODevice::WriteOnly | QIODevice::Append | QIODevice::Text)) {
        qDebug() << "Error: lineQD.txt";
        return;
    }
    QTextStream stream(&file);
    stream << data;
    file.close();
}

QList<QString> SqliteOperate::select_linestations(QString Line_id){
    QString select_sql = "WITH RECURSIVE station_sequence AS ( SELECT line_id, station_id, previous_station_id, next_station_id FROM LineStations WHERE line_id ="+Line_id+" AND station_id = previous_station_id UNION  SELECT e.line_id, e.station_id, e.previous_station_id, e.next_station_id FROM LineStations e  JOIN station_sequence s ON e.station_id = s.next_station_id AND e.line_id = "+Line_id+" AND e.station_id <> e.previous_station_id ) SELECT station_id FROM station_sequence;";
    QList<QString> stringList=sqlite_select_cmd(select_sql);
    return stringList;
}
int SqliteOperate::select_station_in_line(QString Station_id,QString Line_id){
    QString select_sql = "select * from LineStations Where station_id="+Station_id+""+" AND line_id="+Line_id+";";
    qDebug()<<select_sql;
    QList<QString> stringList=sqlite_select_cmd(select_sql);
    if(stringList.size()==0){
        return 0;
    }else{
        return 1;
    }
}
void SqliteOperate::rm_all_file(QString filePath){
    QFile file(filePath);
    if (file.exists()) {
        if (file.remove()) {
//            qDebug() << "文件删除成功";
        } else {
//            qDebug() << "文件删除失败";
        }
    } else {
//        qDebug() << "文件不存在";
    }
}
void SqliteOperate::select_all_data_txt(){
    rm_all_file("lineQD.txt");
    rm_all_file("addline.txt");
    rm_all_file("addstation.txt");
    int i=1;
    QString select_sql="select id from Lines";
    QList<QString> stringList=sqlite_select_cmd(select_sql);
    for (const QString& str : stringList) {
        QString select_sql_totalStations="select count(station_id) from LineStations Where line_id="+str+";";
        QList<QString> stringList_totalStations=sqlite_select_cmd(select_sql_totalStations);
        QString totalStations=stringList_totalStations[0];
        if(totalStations=='0'){
            QString addLine_file="addline.txt";
            QString select_sql="select * from Lines Where id="+str+";";
            QList<QString> stringList=sqlite_select_cmd(select_sql);
            QString name=stringList[1];
            QString colour=stringList[2];
            file_insert(addLine_file,name+" "+colour+"\n\n");
        }else{
            QString select_sql="select * from Lines Where id="+str+";";
            QList<QString> stringList=sqlite_select_cmd(select_sql);
            QString name=stringList[1];
            QString colour=stringList[2];
            QString LineQD_lineinfo="id: "+str+"\n"+"name: "+name+"\n"+"colour: "+colour+"\n";
            file_insert("lineQD.txt",LineQD_lineinfo);

            QString select_sql_from="select station_name from Stations Where id in (select station_id from LineStations Where line_id="+str+" AND "+"station_id=previous_station_id)"+";";
            QList<QString> stringList_start=sqlite_select_cmd(select_sql_from);
            QString fromTo1=stringList_start[0];
            QString select_sql_from1="select station_name from Stations Where id in (select station_id from LineStations Where line_id="+str+" AND "+"station_id=next_station_id)"+";";
            QList<QString> stringList_end=sqlite_select_cmd(select_sql_from1);

            QString fromTo2=stringList_end[0];
            file_insert("lineQD.txt","fromTo: "+fromTo1+" "+fromTo2+"\n");


            file_insert("lineQD.txt","totalStations: "+totalStations+"\n");

            QList<QString> stringList1=select_linestations(str);
            for (const QString& str1 : stringList1){
                QString select_sql="select * from Stations Where id="+str1+";";
                QList<QString> stringList=sqlite_select_cmd(select_sql);
                QString station_name=stringList[1];
                QString latitude=stringList[2];
                QString longitude=stringList[3];
                file_insert("lineQD.txt",QString::number(i)+" "+station_name+" "+longitude+" "+latitude+"\n");
                i++;
            }
            file_insert("lineQD.txt","\n");
            i=1;
        }
    }
    i=0;
    QString addLine_file="addstation.txt";
    QString select_only_station="select * from Stations where id not in (select distinct station_id from LineStations)";
    QList<QString> only_station_list=sqlite_select_cmd(select_only_station);
    QList<QString>list;
    for (const QString& str : only_station_list) {
        list.append(str);
        if(i==3){
            file_insert(addLine_file,list[1]+" "+list[2]+" "+list[3]+"\n");
            list.clear();
            i=0;
        }else{
            i++;
        }
    }
}
QList<QString>  SqliteOperate::select_all_lines(){
    /*
    id INTEGER PRIMARY KEY,
    line_name TEXT,
    line_color TEXT
     */
    QString select_sql = "select * from Lines";
    QList<QString> stringList=sqlite_select_cmd(select_sql);
    return stringList;
}

QList<QString>  SqliteOperate::select_all_satations(){
    /*
    id INTEGER PRIMARY KEY,
    station_name TEXT UNIQUE,
    latitude REAL,
    longitude REAL
     */
    QString select_sql = "select * from Stations;";
    QList<QString> stringList=sqlite_select_cmd(select_sql);
    return stringList;
}
//Insert into Lines (id ,line_name,line_color) values (10,"10号线","#00625")
int SqliteOperate::insert_line(QString line_name, QString line_color) {
    QString cmd_sql = "INSERT INTO Lines (line_name, line_color) VALUES ('" + line_name + "', '" + line_color + "')";
//    qDebug() << cmd_sql;
    return sqlite_commit_cmd(cmd_sql);
}
/*
    id INTEGER PRIMARY KEY,
    station_name TEXT UNIQUE,
    latitude REAL,
    longitude REAL
 */
int SqliteOperate::insert_station(QString station_name, QString latitude,QString longitude) {
    QString cmd_sql = "INSERT INTO Stations (station_name, latitude, longitude) VALUES ('" + station_name + "', " + latitude + "," + longitude + ")";
    return sqlite_commit_cmd(cmd_sql);
}
int SqliteOperate::insert_station_to_line(QString station_name,QString Line_name,QString previous_station_name,QString next_station_name) {
//    int p_newstation_flage=0;
//    int n_newstation_flage=0;
    QString Line_id="";
    QString previous_station_id="";
    QString next_station_id="";
    QString station_id="";
    qDebug()<<station_name<<Line_name<<previous_station_name<<next_station_name;
    QString select_Line_id_sql="select id from Lines Where line_name='"+Line_name+"';";
    QString select_previous_station_id_sql="select id From Stations where station_name='"+previous_station_name+"';";
    QString select_next_station_id_sql="select id From Stations where station_name='"+next_station_name+"';";
    QString select_pnext_station_id_sql="select id From Stations where station_name='"+next_station_name+"';";
    Line_id=sqlite_select_cmd(select_Line_id_sql)[0];
    previous_station_id=sqlite_select_cmd(select_previous_station_id_sql)[0];
    next_station_id=sqlite_select_cmd(select_next_station_id_sql)[0];

    //判断站点前后，防止两站点间插入出错
    QString  select_previous_eq_next="select * from LineStations where station_id="+previous_station_id+" AND "+"next_station_id="+next_station_id+" AND line_id="+Line_id+";";
    qDebug()<<select_previous_eq_next;
    if(select_station_in_line(previous_station_id,Line_id) && select_station_in_line(next_station_id,Line_id) && sqlite_select_cmd(select_previous_eq_next).size()==0){
        QString t;
        t=previous_station_id;
        previous_station_id=next_station_id;
        next_station_id=t;
        qDebug()<<"交换";
    }
//    insert_station(station_name,latitude,longitude);

    QString select_station_id_sql="select id From Stations where station_name='"+station_name+"';";
    qDebug()<<select_station_id_sql;
    station_id=sqlite_select_cmd(select_station_id_sql)[0];
//        qDebug()<<Line_id+" "+previous_station_id+" "+next_station_id+" "+station_id;
    QString insert_sql="insert into LineStations (line_id,station_id,previous_station_id,next_station_id) values ("+Line_id+","+station_id+","+previous_station_id+","+next_station_id+")";
    qDebug()<<insert_sql;



    if(sqlite_commit_cmd(insert_sql)==0){
        //前驱站点
        QString update_lineStations_previous="";
        if(select_station_in_line(previous_station_id,Line_id)==0 && previous_station_id!=station_id ){//不存在于线且上一个站点不等于本站点
            QString inseert_station_LineStation="insert into LineStations (line_id,station_id) values ("+Line_id+","+previous_station_id+")";
            sqlite_commit_cmd(inseert_station_LineStation);
            qDebug()<<inseert_station_LineStation;
            update_lineStations_previous="update LineStations set next_station_id="+station_id+",previous_station_id="+previous_station_id+" where station_id="+previous_station_id+" AND "+"line_id="+Line_id+";";
        }else if(previous_station_id==station_id ){//如果上一站点等于本站点
             update_lineStations_previous="update LineStations set next_station_id="+next_station_id+",previous_station_id="+previous_station_id+" where station_id="+previous_station_id+" AND "+"line_id="+Line_id+";";
        }else{//存在且不相等
            update_lineStations_previous="update LineStations set next_station_id="+station_id+" where station_id="+previous_station_id+" AND "+"line_id="+Line_id+";";
        }
        qDebug()<<update_lineStations_previous;
        if(sqlite_commit_cmd(update_lineStations_previous)==0){

            //后继站点
            QString update_lineStations_next="";
            if(select_station_in_line(next_station_id,Line_id)==0){
                QString inseert_station_LineStation="insert into LineStations (line_id,station_id) values ("+Line_id+","+next_station_id+")";
                sqlite_commit_cmd(inseert_station_LineStation);
                qDebug()<<inseert_station_LineStation;
                 update_lineStations_next="update LineStations set next_station_id="+next_station_id+",previous_station_id="+station_id+" where station_id="+next_station_id+" AND "+"line_id="+Line_id+";";
            }else{
                 update_lineStations_next="update LineStations set previous_station_id="+station_id+" where station_id="+next_station_id+" AND "+"line_id="+Line_id+";";
            }
            qDebug()<<update_lineStations_next;
            if (sqlite_commit_cmd(update_lineStations_next) == 0) {
                // 执行成功的操作
            }else{
                qDebug()<<"error:"<<update_lineStations_next;
                return -1;
            }
        }else{
            qDebug()<<"error:"<<update_lineStations_previous;
            return -1;
        }
    }else{
        qDebug()<<"error:"<<insert_sql;
        return -1;
    }
    return 0;
}


